In this blog, I’m explaining about joins in LINQ in .Net

There are different types of LINQ joins.

1.      Inner join

2.      Left join

3.      Cross join

4.      Group join

Inner join

Inner join returns only those records or rows that match or exists in both the tables.

Example:

var innerjoin = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId             selectnew { d.id, d.FirstName, rl.RoleName, rl.RoleId }).ToList();

Left join

Left join  returns all  records or rows from left table from  right table  returns  only match  records. If there are no columns matching  in the right  table it returns NULL values. It is mandatory to use “INTO” keyword  and “DefaultIFEmpty()” method.

Example:

var emp = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId into t from lj in t.DefaultIfEmpty()                                     selectnew { d.id, d.FirstName, roleid = (int?)d.RoleId, lj.RoleName }).ToList();

Cross Join

Cross join is a Cartesian join means Cartesian product of both the tables.

This join does not need any condition to join two tables. This join returns

records or rows that are multiplication of record number from both the

tables means each row on left table will related to each row of right table.

Example:

var cjoin = (from d in db.EmployeeDetails from o in db.RoleTables selectnew { d.id, d.FirstName, o.RoleId, o.RoleName }).ToList();

Group Join

When a join use a “INTO” expression, then it is called a group join.

Example:

var gjoin = (from d in db.RoleTables join o in db.EmployeeDetails on d.RoleId equals o.RoleId group d bynew { d.RoleId, d.RoleName } into grp orderby grp.Count() selectnew { grp.Key.RoleId, grp.Key.RoleName, count = grp.Count() }).ToList();

 Example

namespace LINQTask
{
    classProgram
    {
        staticvoid Main(string[] args)
        {
         DataClasses1DataContext db = newDataClasses1DataContext();
          //inner joining
            Console.WriteLine("Inner joining");
            var innerjoin = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId selectnew { d.id, d.FirstName, rl.RoleName, rl.RoleId }).ToList();
            foreach (var d in innerjoin)
            {
                Console.WriteLine("Employee Id:{0},Name:{1},Role Id:{2},Role Name:{3}", d.id, d.FirstName.Trim(), d.RoleId, d.RoleName);
            }
            //Left joining
            Console.WriteLine("Left Joining");
            var emp = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId into t from lj in t.DefaultIfEmpty() selectnew { d.id, d.FirstName, roleid = (int?)d.RoleId, lj.RoleName }).ToList();
            foreach (var d in emp)
            {
                Console.WriteLine("Employee Id:{0},Name:{1},Role Id:{2},Role Name:{3}", d.id, d.FirstName.Trim(), d.roleid, d.RoleName);
            }
            //Cross joining
            Console.WriteLine("Cross Joining");
            var cjoin = (from d in db.EmployeeDetails from o in db.RoleTables selectnew { d.id, d.FirstName, o.RoleId, o.RoleName }).ToList();
            foreach (var cobj in cjoin)
            {
                Console.WriteLine("Employee Id:{0},Name:{1},Role Id:{2},Role Name:{3}", cobj.id, cobj.FirstName.Trim(), cobj.RoleId, cobj.RoleName);
            }
 
            //Group join
            Console.WriteLine("Group joining");
            var gjoin = (from d in db.RoleTables join o in db.EmployeeDetails on d.RoleId equals o.RoleId group d bynew { d.RoleId, d.RoleName } into grp orderby grp.Count() selectnew { grp.Key.RoleId, grp.Key.RoleName, count = grp.Count() }).ToList(); ;
            foreach (var cobj in gjoin)
                Console.WriteLine("Role Id:{0},Role Name:{1},count:{2}", cobj.RoleId, cobj.RoleName.Trim(), cobj.count);
 
            Console.ReadKey();
 
        }
    }
}